﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DataLayer
{
    public class dlTaskDetails
    {
        public DataSet TaskDetails(int taskid)
        {
            string connStr = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
            string query = @"
                        SELECT        Tasks.TaskId, Tasks.Titel, Tasks.Description, Tasks.Status, Tasks.UserStoryId, Tasks.EstimatedWorkload, Tasks.RealWorkload, Tasks.UserId, Tasks.Comment, 
                         Impediments.ImpedimentID, Impediments.Titel AS ITitel, Product.ProductId, Product.Name, Sprints.SprintId, Sprints.Titel AS STitel, UserStory.UserStoryId AS USID, 
                         UserStory.Titel AS USTitel, Users.UserID AS Userid, Users.Login, Users.Name AS UName, Users.FirstName
                         FROM            Tasks LEFT JOIN
                         Impediments ON Tasks.TaskId = Impediments.TaskId INNER JOIN
                         Users ON Tasks.UserId = Users.UserID INNER JOIN
                         UserStory ON Tasks.UserStoryId = UserStory.UserStoryId INNER JOIN
                         Sprints INNER JOIN
                         Product ON Sprints.ProductID = Product.ProductId ON UserStory.SprintID = Sprints.SprintId
                        WHERE Tasks.TaskId = @taskid";
            SqlConnection myconn = new SqlConnection(connStr);
            SqlCommand myquery = new SqlCommand(query, myconn);
            myquery.Parameters.AddWithValue("@taskid", taskid);
            SqlDataAdapter mydataadaptor = new SqlDataAdapter(myquery);
            DataSet mydataset = new DataSet();
            mydataadaptor.Fill(mydataset);
            return mydataset;          
        }

        public void InsertCommentDB(int taskid, string comment)
        {
            string connStr = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();

            string insertvalues = "UPDATE Tasks set comment=@comment where TaskId = @taskid";
            SqlCommand query = new SqlCommand(insertvalues, conn);

            query.Parameters.AddWithValue("@taskid", taskid);
            query.Parameters.AddWithValue("@comment", comment);

            query.ExecuteNonQuery();
            conn.Close();

        }

        public void InsertStatusandRealworkloadDB(int taskid, string status,  int realworkload)
        {
            string connStr = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();

            string insertvalues = "UPDATE Tasks set status=@status,RealWorkload=@realworkload  where TaskId = @taskid";
            SqlCommand query = new SqlCommand(insertvalues, conn);

            query.Parameters.AddWithValue("@taskid", taskid);
            query.Parameters.AddWithValue("@status", status );
            query.Parameters.AddWithValue("@realworkload", realworkload);

            query.ExecuteNonQuery();
            conn.Close();
        }
    }
}
